package com.water.app.Impl.aiChat;

import com.water.app.interfaces.aiChat.AiChatMemory;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.ai.chat.client.ChatClient;
import org.springframework.ai.chat.client.advisor.MessageChatMemoryAdvisor;
import org.springframework.ai.chat.memory.ChatMemory;
import org.springframework.ai.chat.memory.MessageWindowChatMemory;
import org.springframework.ai.chat.memory.repository.jdbc.JdbcChatMemoryRepository;
import org.springframework.ai.chat.messages.UserMessage;
import org.springframework.ai.chat.prompt.Prompt;
import org.springframework.ai.chat.prompt.PromptTemplate;
import org.springframework.ai.content.Media;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.util.MimeTypeUtils;
import reactor.core.publisher.Flux;

import java.net.MalformedURLException;
import java.net.URI;
import java.net.URISyntaxException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
public class AiChatMemoryImpl implements AiChatMemory {



    private static final Logger log = LoggerFactory.getLogger(AiChatMemoryImpl.class);
    //最大携带上下文消息数
    private int MAX_MESSAGE=10;

    //智能体抽象
    private final ChatClient chatClient;

    //数据库连接
    private final JdbcTemplate jdbcTemplate;

    //记忆窗口
    private final MessageWindowChatMemory memory;

    //多模态消息
    private List<Media> mediaList;

    //系统背景
    private String system;

    //数据库表结构
    private String TABLE_STRUCTURE;

    public List<Media> getMediaList() {
        return mediaList;
    }

    public void setMediaList(List<Media> mediaList) {
        this.mediaList = mediaList;
    }

    public String getSystem() {
        return system;
    }

    public void setSystem(String system) {
        this.system = system;
    }

    public String getTABLE_STRUCTURE() {
        return TABLE_STRUCTURE;
    }

    public void setTABLE_STRUCTURE(String TABLE_STRUCTURE) {
        this.TABLE_STRUCTURE = TABLE_STRUCTURE;
    }

    public int getMAX_MESSAGE() {
        return MAX_MESSAGE;
    }

    public void setMAX_MESSAGE(int MAX_MESSAGE) {
        this.MAX_MESSAGE = MAX_MESSAGE;
    }

    public ChatClient getChatClient() {
        return chatClient;
    }

    public MessageWindowChatMemory getMemory() {
        return memory;
    }

    public AiChatMemoryImpl(ChatClient.Builder builder, JdbcChatMemoryRepository jdbcChatMemoryRepository,JdbcTemplate jdbcTemplate){
        this.jdbcTemplate=jdbcTemplate;
        memory=MessageWindowChatMemory.builder()
                .chatMemoryRepository(jdbcChatMemoryRepository)
                .maxMessages(MAX_MESSAGE)
                .build();

        chatClient=builder.defaultAdvisors(
                        MessageChatMemoryAdvisor.builder(memory)
                                .build()
                )
                .build();
    }
    // 正则规则：匹配 FROM（大小写不敏感）+ 任意空白字符 + 捕获表名（到第一个非字母数字下划线为止）
    private static final Pattern TABLE_NAME_PATTERN =
            Pattern.compile("(?i)from\\s+(\\w+)", Pattern.CASE_INSENSITIVE);

    public static String extractFirstTableName(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return null;
        }
        Matcher matcher = TABLE_NAME_PATTERN.matcher(sql.trim());
        if (matcher.find()) { // 找到匹配的内容
            return matcher.group(1); // 返回第一个捕获组（即表名）
        }
        return null; // 未匹配到（如 SQL 中无 FROM 关键字）
    }
    public Map<String,Object> substringTable(String question,Prompt prompt){
        Map<String,Object> tableDateList=new HashMap<>();
        String sqlPrompt="重点关注："+prompt+"\n用户问题："+question+"\n数据表结构:\n" +
                TABLE_STRUCTURE+"\n,请你根据用户问题和数据表结构生成一个查询sql，" +
                "查询用户想问的问题，注意！！！" +
                "1.生成MySQL的sql，严格sql，不需要其他符号，只生成sql，也不要加注释\n" +
                "2.只生产SELECT查询语句，不能生成其他新增删除修改的语句\n" +
                "3.字段名、表名必须和表结构一致，大小写匹配\n" +
                "4.如果有需要，可用查询多个表，多个查询sql用\";\"号分隔";
        String sqls = chatClient.prompt(prompt)
                .user(sqlPrompt)
                .advisors(a -> a.param(ChatMemory.CONVERSATION_ID, "新增sql与推荐"))
                .call()
                .content()
                .trim();
        String[] s = sqls.split(";");
        for (String sql:s){
            String tableName = extractFirstTableName(sql);
            List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
            tableDateList.put(tableName,maps);
        }
        return tableDateList;
    }
    private final PromptTemplate promptTemplate=PromptTemplate.builder()
            .template("你现在正在回答{userId}的问题，请你重点关注该用户的订单表的订单判断喜好")
            .build();
    @Override
    public String MySqlChatMemory(String question, String url, Integer userId) throws URISyntaxException, MalformedURLException {
        if(url!=null&&this.mediaList.isEmpty()){
            URI uri = new URI(url).toURL().toURI();
            this.mediaList= List.of(new Media(MimeTypeUtils.IMAGE_PNG,uri));
        }

        //设置可变的提示词
        Prompt prompt = promptTemplate.create(Map.of("userId", userId));
        Map<String, Object> tables = substringTable(question,prompt);
        log.info("sql查询结构查询:{}",tables);

        return chatClient.prompt(prompt)
                .advisors(a->a.param(ChatMemory.CONVERSATION_ID,String.valueOf(userId)))
                .messages(
                        UserMessage.builder().media(mediaList).text("用户的问题："+question+"\n" +
                        "sql查询结果："+tables+
                        "\n请用简洁的自然语言回复用户，不要暴露SQL语句，只说结果。")
                                .metadata(new HashMap<>())
                                .build()
                )
                .system(system)
                .call()
                .content();
    }
    public Flux<String> MySqlChatMemoryStream(String question, String url, Integer userId) throws Exception {

        //设置可变的提示词
        Prompt prompt = promptTemplate.create(Map.of("userId", userId));
        Map<String, Object> tables = substringTable(question,prompt);
        log.info("sql查询结构查询:{}",tables);
        if(url!=null&&this.mediaList.isEmpty()){
            URI uri = new URI(url).toURL().toURI();
            this.mediaList= List.of(new Media(MimeTypeUtils.IMAGE_PNG,uri));
            return chatClient.prompt(prompt)
                    .advisors(a->a.param(ChatMemory.CONVERSATION_ID,String.valueOf(userId)))
                    .messages(
                            UserMessage.builder().media(mediaList).text("用户的问题："+question+"\n" +
                                            "sql查询结果："+tables+
                                            "\n请用简洁的自然语言回复用户，不要暴露SQL语句，只说结果。")
                                    .metadata(new HashMap<>())
                                    .build()
                    )
                    .system(system)
                    .stream()
                    .content();
        }else {
            return chatClient.prompt(prompt)
                    .advisors(a->a.param(ChatMemory.CONVERSATION_ID,String.valueOf(userId)))
                    .user("用户的问题："+question+"\n" +
                            "sql查询结果："+tables+
                            "\n请用简洁的自然语言回复用户，不要暴露SQL语句，只说结果。")
                    .system(system)
                    .stream()
                    .content();
        }

    }

}
